clear all 

* Import data from CSV
import delimited "$input/collateralized_by_type.csv"  

* Calculate the loan volume that is collateralized
gen collateralized_volume = loan_amount if collateralized == 1

* Generate totals across all categories
egen total_volume = total(loan_amount)
egen total_collateralized = total(collateralized_volume)

* Collapse data to get sum of loan amounts and collateralized volumes by category
collapse (sum) loan_amount collateralized_volume, by(category)

* Add a row for overall totals
local N = _N + 1          // Increase observation count by 1
set obs `N'               // Add the additional observation
replace category = "Overall" in `N'  // Label the new observation as "Overall"

* Recalculate overall totals for the new dataset
egen total_volume = total(loan_amount)
egen total_collateralized = total(collateralized_volume)

* Compute percentage of loan volume by category
gen pct_loan_volume = (loan_amount / total_volume)*100
replace pct_loan_volume = 100 in `N'  // Set overall percentage to 100%

* Compute percentage of collateralized volume within each category
gen pct_collateralized_volume = (collateralized_volume / loan_amount)*100
replace pct_collateralized_volume = (total_collateralized / total_volume)*100 in `N'  // Overall collateralized percentage

* Prepare data for LaTeX export by creating a matrix
mkmat pct_loan_volume pct_collateralized_volume, matrix(result)

* Extract category labels and explicitly name matrix rows
levelsof category, local(categories)
matrix rownames result = `categories'

* Label matrix columns clearly
matrix colnames result = "% of loan volume" "% of loan collateralized (volume)"

* Export matrix to a LaTeX table using esttab
esttab matrix(result) using ///
"C:\Users\pbeaum\Beaumont Dropbox\Paul Beaumont\FinTech\feb24_version\tables\collateralized.tex", ///
replace booktabs nomtitle nonumber ///
collabels(none) alignment(l c c)  
